﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;

namespace EWDT_CDS_Hub.App_Code
{
    public class SubjectToDiploma
    {
        public String Diploma_id { get; set; }
        public String Subject_id { get; set; }

        public static List<SubjectToDiploma> GetStudentToDiploma()
        {
            List<SubjectToDiploma> results = new List<SubjectToDiploma>();
            SqlConnection conn = new SqlConnection();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EWDTDBString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM SubjectToDiploma";
                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    SubjectToDiploma s = new SubjectToDiploma();
                    s.Diploma_id= (String)dr["diploma_id"];
                    s.Subject_id = (String)dr["subject_id"];

                    results.Add(s);
                }
            }
            catch (SqlException e)
            {
            }
            finally
            {
                conn.Close();
            }

            return results;
        }

        public static int InsertSubjectToDiploma(SubjectToDiploma s)
        {
            int rowsInserted = 0;
            SqlConnection conn = new SqlConnection();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EWDTDBString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO SubjectToDiploma (diploma_id, subject_id) VALUES (@did, @sid)";
                comm.Parameters.AddWithValue("@did", s.Diploma_id);
                comm.Parameters.AddWithValue("@sid", s.Subject_id);
                rowsInserted = comm.ExecuteNonQuery();

            }
            catch (SqlException ex)
            {

            }
            finally
            {
                conn.Close();
            }

            return rowsInserted;
        }

        //retrieve list of subject the diploma cannot take.
        public static List<Subject> RetrieveCDSOfDiploma(String id)
        {
            List<Subject> results = new List<Subject>();
            SqlConnection conn = new SqlConnection();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EWDTDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Subject WHERE subject_id IN (SELECT subject_id FROM SubjectToDiploma WHERE diploma_id = @id)";
                comm.Parameters.AddWithValue("@id", id);
                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    Subject s = new Subject();
                    s.Subject_id = (String)dr["subject_id"];
                    s.Subject_name = (String)dr["subject_name"];
                    s.Subject_desc = (String)dr["subject_desc"];
                    s.Subject_type = (String)dr["subject_type"];
                    results.Add(s);
                }

                dr.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return results;
        }

        //retrieve list of diploma that cannot take that subject
        public static List<Diploma> RetrieveDiplomaOfCDS(String id)
        {
            List<Diploma> results = new List<Diploma>();
            SqlConnection conn = new SqlConnection();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EWDTDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Diploma WHERE diploma_id IN (SELECT diploma_id FROM SubjectToDiploma WHERE subject_id = @id)";
                comm.Parameters.AddWithValue("@id", id);
                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    Diploma d = new Diploma();
                    d.Diploma_id = (String)dr["diploma_id"];
                    d.Diploma_name = (String)dr["diploma_name"];
                    d.School_id = (String)dr["school_id"];
                    results.Add(d);
                }

                dr.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return results;
        }

        //check if diploma can take that subject
        public static bool CheckDiploma(Diploma d, Subject s)
        {
            bool result = true;
            List<Subject> subjectList = SubjectToDiploma.RetrieveCDSOfDiploma(d.Diploma_id);

            if (subjectList.Count == 0) //if diploma can take all subjects.
            {
                return result;
            }
            else
            {
                foreach (Subject subject in subjectList)
                {
                    if (s.Subject_id == subject.Subject_id)
                    {
                        result = false;
                        break;
                    }
                }
            }
            return result;
        }

    }
}